package com.wm.dao.hibernate;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.wm.bean.Address;
import com.wm.bean.Area;
import com.wm.bean.Order;
import com.wm.bean.Orders;
import com.wm.comm.Constant;
import com.wm.dao.OrderDAO;
import com.wm.dao.OrdersDAO;

/**
 * @author yangbo
 *
 * 2012-12-26
 */
public class OrdersHibernateDAO extends HibernateDaoSupport implements OrdersDAO {

	private static final String LOAD_BY_ID = "from Orders where id = ?";
	private static final String REMOVE_BY_ID = "delete Orders Admin where id = ?";
	private static final String LOAD_BY_USER_ID_STATE ="from Orders where user_id = ? and state = ?";

	/**
	 * 保存Orders对象
	 * 
	 * @param orders
	 * 			Orders
	 * @return Orders
	 * 			Orders
	 */
	public Orders saveOrders(Orders orders){
		this.getHibernateTemplate().save(orders);
		return orders;
	}
	
	/**
	 * 更新Orders对象
	 * 
	 * @param orders
	 * 			Orders
	 * @return Orders
	 * 			Orders
	 */
	public Orders updateOrders(Orders orders){
		this.getHibernateTemplate().update(orders);
		return orders;
	}
	
	/**
	 * 根据ID删除Orders对象
	 * 
	 * @param id
	 * 			long
	 */
	public void removeOrdersById(final long id){
		getHibernateTemplate().execute(new HibernateCallback() {
	        public Object doInHibernate(Session s) throws HibernateException, SQLException {
	          Query query = s.createQuery(REMOVE_BY_ID);
	          query.setLong(0, id);
	          query.executeUpdate();
	          return null;
	        }
	      });
	}
	
	/**
	 * 根据ID获取Orders对象
	 * 
	 * @param id
	 * 			long
	 * @return Orders
	 * 			Orders
	 */
	public Orders findOrdersByid(long id){
		List l = this.getHibernateTemplate().find(LOAD_BY_ID,id);
		if(l == null || l.isEmpty()){
			return null;
		}else{
			return (Orders) l.get(0);
		}
	}
	
	/**
	 * 获取Orders对象列表
	 * @param state
	 * @param orderby
	 * @param ascOrDesc
	 * @param firstResult
	 * @param maxResults
	 * @return List
	 * 			List
	 */
	public List findOrdersByState(int state,String orderby,int ascOrDesc,final int firstResult,final int maxResults){
		StringBuffer sb = new StringBuffer();
		sb.append("from Orders where 1=1");
		if (state != -1) {
			sb.append(" and state=");
			sb.append(state);
		}
		if (StringUtils.isNotBlank(orderby)) {
			if (ascOrDesc == Constant.ORDER_ASC) {
				sb.append(" order by " + orderby+" asc");
			}
			if (ascOrDesc == Constant.ORDER_DESC) {
				sb.append(" order by " + orderby+" desc");
			}
		}
		final String sql = sb.toString();
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException, SQLException {
				Query query = s.createQuery(sql);
				List list = query.list();
				query.setFirstResult(firstResult);
				query.setMaxResults(maxResults);
				return list;
			}
		});
	}
	
	/**
	 * 获取Orders对象个数
	 * @param state
	 * @return long
	 * 			long
	 */
	public long getOrderNumByState(int state){
		StringBuffer sb = new StringBuffer();
		sb.append("select count(id) from Orders where 1=1");
		if (state != -1) {
			sb.append(" and state=");
			sb.append(state);
		}
		List l = this.getHibernateTemplate().find(sb.toString());
		
		if (l == null || l.isEmpty()) {
			return 0;
		} else {
			return (Long) l.get(0);
		}
	}
	
	/**
	 * 根据userId,findType查询记录
	 * @param userId
	 * @param findType
	 * @param orderby
	 * @param ascOrDesc
	 * @param firstResult
	 * @param maxResults
	 * @return
	 */
	public List findOrdersByUserIdFindType(long userId,int findType,String orderby,int ascOrDesc,final int firstResult,final int maxResults){
		StringBuffer sb = new StringBuffer();
		sb.append("from Orders where 1=1");
		sb.append(" and userId = "+userId);
		//查当天
		if(findType==1){
			sb.append(" and TO_DAYS(addTime) = TO_DAYS(NOW())");
		}
		//查最近七天
		else if(findType==7){
			sb.append(" and TO_DAYS(NOW())-TO_DAYS(addTime)<8");
		}
		//查最近三十天
		else if(findType==30){
			sb.append(" and TO_DAYS(NOW())-TO_DAYS(addTime)<31");
		}
		//查三十天之外的
		else if(findType>30){
			sb.append(" and TO_DAYS(NOW())-TO_DAYS(addTime)>30");
		}
		if (StringUtils.isNotBlank(orderby)) {
			if (ascOrDesc == Constant.ORDER_ASC) {
				sb.append(" order by " + orderby+" asc");
			}
			if (ascOrDesc == Constant.ORDER_DESC) {
				sb.append(" order by " + orderby+" desc");
			}
		}
		final String sql = sb.toString();
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException, SQLException {
				Query query = s.createQuery(sql);
				List list = query.list();
				query.setFirstResult(firstResult);
				query.setMaxResults(maxResults);
				return list;
			}
		});
	}
	
	/**
	 * 根据userId,findType查询记录获取Orders对象个数
	 * @return
	 */
	public long getOrderNumByUserIdFindType(long userId,int findType){
		StringBuffer sb = new StringBuffer();
		sb.append("select count(id) from Orders where 1=1");
		sb.append(" and userId = "+userId);
		//查当天
		if(findType==1){
			sb.append(" and TO_DAYS(addTime) = TO_DAYS(NOW())");
		}
		//查最近七天
		else if(findType==7){
			sb.append(" and TO_DAYS(NOW())-TO_DAYS(addTime)<8");
		}
		//查最近三十天
		else if(findType==30){
			sb.append(" and TO_DAYS(NOW())-TO_DAYS(addTime)<31");
		}
		//查三十天之外的
		else if(findType>30){
			sb.append(" and TO_DAYS(NOW())-TO_DAYS(addTime)>30");
		}
		List l = this.getHibernateTemplate().find(sb.toString());
		
		if (l == null || l.isEmpty()) {
			return 0;
		} else {
			return (Long) l.get(0);
		}
	}
	
	/**
	 * 根据条件查询记录
	 * @param userId
	 * @param state
	 * @return
	 */
	public Orders findOrdersByUserIdState(long userId,int state){
		Object[] obj={new Long(userId),new Integer(state)};
		List l = this.getHibernateTemplate().find(LOAD_BY_USER_ID_STATE,obj);
		if(l == null || l.isEmpty()){
			return null;
		}else{
			return (Orders) l.get(0);
		}
	}
	
	/**
	 * 根据resId,time查询记录
	 * @param resId
	 * @param time
	 * @param orderby
	 * @param ascOrDesc
	 * @param firstResult
	 * @param maxResults
	 * @return
	 */
	public List findOrdersByResIdTime(long resId,int time,String orderby,int ascOrDesc,final int firstResult,final int maxResults){
		StringBuffer sb = new StringBuffer();
		sb.append("from Orders where 1=1");
		if (resId != -1) {
			sb.append(" and resId=");
			sb.append(resId);
		}
		if(time != -1){
			sb.append(" and (TO_DAYS(addTime) - TO_DAYS(NOW()) <="+time+")");
		}
		if (StringUtils.isNotBlank(orderby)) {
			if (ascOrDesc == Constant.ORDER_ASC) {
				sb.append(" order by " + orderby+" asc");
			}
			if (ascOrDesc == Constant.ORDER_DESC) {
				sb.append(" order by " + orderby+" desc");
			}
		}
		final String sql = sb.toString();
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException, SQLException {
				Query query = s.createQuery(sql);
				List list = query.list();
				query.setFirstResult(firstResult);
				query.setMaxResults(maxResults);
				return list;
			}
		});
	}
	
	/**
	 * 根据根据resId,time查询记录获取Orders对象个数
	 * @param resId
	 * @param time
	 * @return
	 */
	public long getOrderNumByResIdTime(long resId,int time){
		StringBuffer sb = new StringBuffer();
		sb.append("select count(id) from Orders where 1=1");
		if (resId != -1) {
			sb.append(" and resId=");
			sb.append(resId);
		}
		if(time != -1){
			sb.append(" and (TO_DAYS(addTime) - TO_DAYS(NOW()) <="+time+")");
		}
		List l = this.getHibernateTemplate().find(sb.toString());
		
		if (l == null || l.isEmpty()) {
			return 0;
		} else {
			return (Long) l.get(0);
		}
	}
}
